﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace stuManage
{
    public partial class teaCourse : System.Web.UI.Page
    {
        static List<Course> courses = new List<Course>();
        DBHelper helper = new DBHelper();//用于执行数据库操作的对象

        protected void Page_Load(object sender, EventArgs e)
        {
           
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            addCourse.Style["Display"] = "None"; //显示
            notFound.InnerHtml = "";

            string tea_id = TextBox1.Text.Trim();
                //先查询是否存在该学生
                string strConn = "server=localhost;port=3306;user id=root;password=lxy1756431734;database=student; pooling=true;";
                MySqlConnection conn = new MySqlConnection(strConn);
                conn.Open();//链接数据库  
                DataSet ds = new DataSet();
                string sql = "select * from teacher_inf where id='" + tea_id + "'";
                MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                da.Fill(ds, "table");
                if (ds.Tables["table"].Rows.Count > 0)
                {
                    addCourse.Style["Display"] = "Block"; //显示
                    showInfo();

            }
            else
                {
                string HTML = "<p>未找到该教师信息,请检查职工号是否输入正确!</p>";
                notFound.InnerHtml = HTML;
            }
            conn.Close();


        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList2.Items.Clear();
            string department = DropDownList1.SelectedValue;
            string strConn = "server=localhost;port=3306;user id=root;password=lxy1756431734;database=student; pooling=true;";
            MySqlConnection conn = new MySqlConnection(strConn);
            conn.Open();//链接数据库  
            DataSet ds = new DataSet();
            string sql = "select id,name from course_inf where department ='{0}'";
            sql = string.Format(sql, department);

            MySqlCommand com = new MySqlCommand(sql, conn);
            MySqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                DropDownList2.Items.Add(new ListItem(dr["name"].ToString(), dr["id"].ToString()));
            }

        }
        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
            string sqltxt = "delete from tea_course  where  id='" + id + "' ";
            string strConn = "server=localhost;port=3306;user id=root;password=lxy1756431734;database=student; pooling=true;";
            MySqlConnection conn = new MySqlConnection(strConn);
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(sqltxt, conn);
            cmd.ExecuteNonQuery();
            showInfo(); //  绑定控件显示最后结果

        }
        public void showInfo()
        {
            string tea_id = TextBox1.Text.Trim();
            if (tea_id.Length > 0)
            {
                string strConn = "server=localhost;port=3306;user id=root;password=lxy1756431734;database=student; pooling=true;";
                MySqlConnection conn = new MySqlConnection(strConn);
                conn.Open();//链接数据库  
                DataSet ds = new DataSet();
                //  string sql = "select stu_course.id as 序号,student_inf.id as 学号,student_inf.name as 姓名,department as 系部,className as 班级,course_inf.id as 课程编号,course_inf.name as 课程名称 from student_inf, course_inf, stu_course where student_inf.id = {0} and student_inf.id = stu_id and course_inf.id = course_id";
                string sql = "select tea_course.id,teacher_inf.id as tea_id,teacher_inf.name as tea_name ,teacher_inf.department,course_inf.id as cou_id,course_inf.name as cou_name from teacher_inf, course_inf, tea_course where teacher_inf.id = {0} and teacher_inf.id = tea_id and course_inf.id = course_id";

                sql = string.Format(sql, tea_id);
                MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                da.Fill(ds, "table");
                if (ds.Tables["table"].Rows.Count == 0)
                {
                    string HTML = "<p>该教师还没有课程，点击下方按钮添加!</p>";
                    notFound.InnerHtml = HTML;
                }
                GridView1.DataSource = ds.Tables["table"];
                GridView1.Columns[0].Visible = false;
                GridView1.DataBind();

            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string cou_id = DropDownList2.SelectedValue;
            if (cou_id.Length > 0)
            {
                string stu_id = TextBox1.Text.Trim();
                string commandString = "insert into tea_course values(null,'{0}','{1}')";
                commandString = string.Format(commandString, stu_id, cou_id);
                helper.ExecuteSQL(commandString);
                showInfo();

            }
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)
                {
                    ((LinkButton)e.Row.Cells[6].Controls[0]).Attributes.Add("onclick", "javascript:return confirm('确认删除?')");
                }
            }
        }
    }
}